<?php

require_once("dao/Dao.php");
require_once('dao/OffreVgaDao.php');
require_once('dao/OffreVoipDao.php');
require_once('dao/OffreFixeDao.php');
require_once('dao/OffreVenteOptionFixeDao.php');
require_once('dao/OffreInternetDao.php');

/**
 * DAO de l'objet Client.
 *
 * @author Ludovic
 */
class ClientDao extends Dao {

    protected static $tableName = "fiche_client";

    /**
     * Retourne les clients ayant des appels passés pour une période donnée
     * 
     * @param DateTime[] $period
     * @return array
     */
    public static function findByCdr(array $period = null) {
        global $bdd;
        // colonnes clients
        $clientData       = array('code_client_hto', 'nom', 'prenom');
        // colonnes detail client
        $detailClientData = array('ndi', 'sda', 'type_offre', 'offre',
            'parent_abonnement', 'grille_tarifaire', 'date_souscription',
            'facturee', 'abonnement', 'minute_national', 'minute_international',
            'nbr_offre', 'prix');
        // colonnes cdr
        $cdrData          = array('caller_num', 'called_num', 'starting_time', 'type', 'duration');

        $q = 'SELECT ' .
                'fc.id as id_client, fc.' . implode(', fc.', $clientData) . ', ' .
                'dc.id as id_detail_client, dc.' . implode(', dc.', $detailClientData) . ', ' .
                'cdr.id as id_cdr, cdr.' . implode(', cdr.', $cdrData) . ' ' .
                'FROM fiche_client fc ' .
                'INNER JOIN detail_client dc ON fc.code_client_hto = dc.code_client_hto '.
                'INNER JOIN cdr_fixe cdr ON dc.id = cdr.id_detail_client ';

        if (null !== $period) {
            $q .= 'WHERE DATE(cdr.starting_time) BETWEEN :period_start AND :period_end'
                    . ' OR '
                    . '( STR_TO_DATE(dc.date_souscription, "%d/%m/%Y") BETWEEN :period_start_sous  AND :period_end_sous '
                    . 'AND DATE(cdr.starting_time) BETWEEN :period_start_sous  AND :period_end)';
        }

        try {
            $stmt = $bdd->prepare($q);
            if (null !== $period) {
                $stmt->bindParam(':period_start', $period[0]->format('Y-m-d'));
                $stmt->bindParam(':period_end', $period[1]->format('Y-m-d'));

                // Période de souscription
                $sousPeriod = array($period[0]->modify('-1 month'), $period[1]->modify('-1 month'));
                $stmt->bindParam(':period_start_sous', $sousPeriod[0]->format('Y-m-d'));
                $stmt->bindParam(':period_end_sous', $sousPeriod[1]->format('Y-m-d'));
            }
            $bdd->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $stmt->execute();
            $results = $stmt->fetchall(PDO::FETCH_ASSOC);
            $stmt->closeCursor();

            // Formattage des résultats
            $ret = array();
            foreach ($results as $key => $result) {
                // Construction client
                foreach ($clientData as $data) {
                    $ret['clients'][$result['id_client']][$data] = $result[$data];
                }
                // Construction detail_client (abonnement)
                foreach ($detailClientData as $data) {
                    // Correction date sous
                    if($data === 'date_souscription') {
                        $result[$data] = \DateTime::createFromFormat('d/m/Y', $result[$data]);
                    }
                    
                    $ret['clients'][$result['id_client']]['details'][$result['id_detail_client']]['abonnement'][$data] = $result[$data];
                }
                // Construction cdr (appels)
                foreach ($cdrData as $data) {
                    // starting time
                    if($data === 'starting_time') {
                        $result[$data] = \DateTime::createFromFormat('Y-m-d H:i:s', $result[$data]);
                    }
                    $ret['clients'][$result['id_client']]['details'][$result['id_detail_client']]['appels'][$result['id_cdr']][$data] = $result[$data];
                }
            }
            return $ret;
        } catch (PDOException $e) {
            echo $e->getMessage();
        }
        return $stmt;
    }

    /**
     * Suppression d'un client
     */
    public static function delete($id) {
        global $bdd, $logger;

        $logger->info("Suppression du client : " + $id);

        $requete_del_user = $bdd->prepare("DELETE FROM fiche_client WHERE code_client_hto ='" . $id . "' ");
        $requete_del_user->execute();

        // Suppression de la fiche client
        $requete = $bdd->prepare("DELETE FROM fiche_client WHERE code_client_hto ='" . $id . "' ");
        if ($requete->execute()) {

            // Suppression des détails du client
            $requete = $bdd->prepare("DELETE FROM detail_client WHERE code_client_hto ='" . $id . "' ");

            if ($requete->execute()) {
                // La suppression c'est correctement réalisée.
                return true;
            }
        }
        $logger->error("Erreur lors de la suppression du client : " + $id);

        // La suppression a produit une erreur
        return false;
    }

    /**
     * Recherche des clients 
     */
    public static function getAll() {
        return parent::find(ClientDao::$tableName);
    }

    /**
     * Recherche d'un client par code client hto
     */
    public static function searchById($id) {
        return parent::find(ClientDao::$tableName, "code_client_hto='" . $id . "'");
    }

    /**
     * Recherche des offres par raison social  et nom
     */
    public static function searchByName($name) {
        return parent::find(ClientDao::$tableName, "nom LIKE '%" . $name . "%' OR raison_social LIKE '%" . $name . "%'");
    }

    /**
     * Recherche des clients par sim
     */
    public static function searchBySim($sim) {
        return parent::query('SELECT f.* FROM fiche_client f,detail_client c WHERE f.code_client_hto=c.code_client_hto AND c.num_sim="' . $sim . '"');
    }

    /**
     * Recherche des clients par sim pour sim/swap
     */
    public static function searchBySimswap($sim) {
        return parent::query('SELECT * FROM `detail_client` WHERE num_sim="' . $sim . '"');
    }

    /**
     * Recherche des clients par numero
     */
    public static function searchByNumero($numero) {
        return parent::query('SELECT distinct f.* FROM detail_client d,fiche_client f WHERE f.code_client_hto=d.code_client_hto AND (d.ndi="' . $numero . '"
				OR d.num_sda_voip="' . $numero . '" OR d.num_sda_nhto="' . $numero . '")');
    }

    /**
     * Mise à jour de ligne pour le detail de facture
     */
    public static function UpdateDetailFacture($ndi, $choix) {
        parent::exec("UPDATE detail_client set facture_detail = '" . $choix . "' WHERE ndi = '" . $ndi . "' ");
    }

    /**
     * Changement de statut de la ligne
     */
    public static function ChangeLineStatus($ndi, $statut) {
        parent::exec("UPDATE detail_client SET ligne_statut = '" . $statut . "' WHERE ndi = '" . $ndi . "' ");
    }

    /**
     * Update compte client et utilisateurs
     */
    public static function update($code_client_hto, $nom, $prenom, $cp, $adresse, $ville, $pays, $raison_social, $forme_juridique, $rcs, $ape, $activite, $tel_fix, $tel_fax, $tel_mobile, $email, $site_internet, $siret, $rib, $code_iban, $code_etablissement, $swift, $code_guichet, $num_compte, $banque, $adresse_bq, $cp_banque, $ville_bq, $mode_paiement, $civilite, $raison_social_dif, $adresse_dif, $cp_dif, $ville_dif, $telephone_dif, $email_gest, $site_internet, $facture_detail, $vendeur) {
        echo " WHERE code_client_hto = '" . $code_client_hto . "' ";


        return parent::exec("UPDATE fiche_client set nom='" . $nom . "', prenom='" . $prenom . "', adresse='" . $adresse . "', cp='" . $cp . "', ville='" . $ville . "', pays='" . $pays . "',raison_social='" . $raison_social . "',forme_juridique='" . $forme_juridique . "' , rcs='" . $rcs . "', ape='" . $ape . "', activite='" . $activite . "', tel_fix='" . $tel_fix . "', tel_fax='" . $tel_fax . "', tel_mobile='" . $tel_mobile . "', email='" . $email . "', site_internet='" . $site_internet . "', siret='" . $siret . "', rib='" . $rib . "',code_iban='" . $code_iban . "',code_etablissement='" . $code_etablissement . "', swift='" . $swift . "', code_guichet='" . $code_guichet . "'
				, num_compte='" . $num_compte . "', banque='" . $banque . "', adresse_bq='" . $adresse_bq . "', cp_banque='" . $cp_banque . "', ville_bq='" . $ville_bq . "', mode_paiement='" . $mode_paiement . "', civilite='" . $civilite . "', raison_social_dif='" . $raison_social_dif . "', adresse_dif='" . $adresse_dif . "', cp_dif='" . $cp_dif . "', ville_dif='" . $ville_dif . "', telephone_dif='" . $telephone_dif . "', email_gest='" . $email_gest . "', site_internet='" . $site_internet . "', facture_detail='" . $facture_detail . "', vendeur='" . $vendeur . "'
				
				
				where code_client_hto=" . $code_client_hto . "");
    }

    /**
     * Update login password
     */
    public static function update1($code_client_hto, $login, $password) {


        parent::exec("UPDATE utilisateurs SET  password=PASSWORD('" . $password . "'), username='" . $login . "' WHERE code_client_hto = '" . $code_client_hto . "' ");

        return parent::exec("UPDATE fiche_client set
				 login='" . $login . "'
				, password=PASSWORD('" . $password . "')
				where code_client_hto=" . $code_client_hto . "");
    }

    /**
     * detail offre fixe
     */
    public static function searchByoffrefixe($id) {
        //return parent::query("SELECT d.*,f.raison_social,v.nom as article FROM detail_client  d,fiche_client f, ve_offre_vga v WHERE f.code_client_hto='".$id."' and `offre_nhto`<>'' and d.code_client_hto=f.code_client_hto and d.offre_nhto = v.code_article
        //union
        //SELECT d.*,f.raison_social,v.nom as article FROM detail_client  d,fiche_client f, ve_offre_vga v WHERE f.code_client_hto='".$id."' and `type_forfait_nhto`<>'' and d.code_client_hto=f.code_client_hto and d.type_forfait_nhto = v.code_article");
        return parent::query("SELECT * FROM `detail_client` WHERE code_client_hto='" . $id . "' and `type_offre` IN ('vga', 'vga_num', 'voip') ");
    }

    /**
     * detail offre fixe
     */
    public static function searchByoffrevoip($id) {
        return parent::query("SELECT * FROM `detail_client` WHERE code_client_hto='" . $id . "' and `type_offre`='voip'");
    }

    /**
     * detail offre internet 
     */
    public static function searchByoffreinternet($id) {
        return parent::query("SELECT * FROM `detail_client` WHERE code_client_hto='" . $id . "' and `type_offre`='xdsl' ");
    }

    /**
     * detail offre mobile
     */
    public static function searchByoffremobile($id) {
        return parent::query("SELECT * FROM `detail_client` WHERE code_client_hto='" . $id . "' and type_offre = 'mobile' ");
    }

    /**
     * detail offre mobile
     */
    public static function searchByoffremobile_pending($id) {
        return parent::query("SELECT * FROM `detail_client_pending` WHERE code_client_hto='" . $id . "' and `type_forfait_mobile`<>'' ");
    }

    /**
     * detail option mobile
     */
    public static function searchByoptionmobile($id) {
        return parent::query("SELECT * FROM `detail_client` WHERE code_client_hto='" . $id . "' and `type_offre`='mobile_option' ");
    }
	
	 /**
     * récupère la ligne par l'id
     */
    public static function searchByLigneid($id) {
        return parent::query("SELECT * FROM `detail_client` WHERE id='" . $id . "' ");
    }

    /**
     * liste des lignes detail lié au ndi
     */
    public static function searchBymobile_ndi($ndi) {
        return parent::query("SELECT * FROM `detail_client` WHERE ndi='" . $ndi . "' ");
    }

    /**
     * liste options mobile lié au ndi
     */
    public static function searchByoptionmobile_ndi($ndi) {
        return parent::query("SELECT * FROM `detail_client` WHERE ndi='" . $ndi . "' and `type_offre`='mobile_option' ");
    }

    /**
     * detail offre mobile lié au ndi 
     */
    public static function searchByoffremobile_ndi($ndi) {
        return parent::query("SELECT * FROM `detail_client` WHERE ndi='" . $ndi . "' and type_offre = 'mobile' ");
    }

    public static function deleteoffre($id) {
        //var_dump("DELETE FROM detail_client WHERE id ='".$id."' ");
        return parent::query("DELETE FROM detail_client WHERE id ='" . $id . "' ");
    }

    public static function deleteOffreNdi($ndi) {
        return parent::query("DELETE FROM detail_client WHERE ndi ='" . $ndi . "' ");
    }

    /**
     * liste des lignes detail en pending lié au ndi
     */
    public static function searchBymobile_pending($ndi) {
        $date_mois_precedent = date("Y-m-d H:i:s", strtotime('first day of this month'));
        return parent::query("SELECT *  FROM `detail_client_pending` WHERE ndi = '" . $ndi . "' AND (`option_mobile` <> '' OR `commande_date` < '" . $date_mois_precedent . "') ");
    }

    /**
     * déplacement des offres/options du detail pending au detail
     */
    public static function moveFromPending($ndi, $id) {
        $date_mois_precedent = date("Y-m-d H:i:s", strtotime('first day of this month'));
        $test_pending        = parent::query("SELECT * FROM detail_client_pending WHERE (`option_mobile` <> '' OR `commande_date` < '" . $date_mois_precedent . "') AND id = '" . $id . "' ");

        if ($test_pending) {
            $offre      = '';
            $type_offre = '';

            $ligne = parent::queryOne("SELECT id,code_client_hto, ndi, type_forfait_mobile, prix_forfait_mobile, option_mobile, prix_option_mobile, num_sim, rio, code_puk, utilisateur, commande_date, engagement, date_souscription, fin_engagement, quota_temps, quota_sms, quota_data, forfait_jours, forfait_horaires, com_temps, com_sms, com_data FROM detail_client_pending WHERE (`option_mobile` <> '' OR `commande_date` < '" . $date_mois_precedent . "') AND id = " . $id . " ");

            //recupere le nom de l'offre ou de l'option
            if ($ligne['type_forfait_mobile'] <> '') {
                $offre      = $ligne['type_forfait_mobile'];
                $type_offre = "mobile";
            } elseif ($ligne['option_mobile'] <> '') {
                $offre      = $ligne['option_mobile'];
                $type_offre = "mobile_option";
            }

            //recupere le prix de l'offre ou de l'option
            if ($ligne['prix_forfait_mobile'] <> '') {
                $prix = $ligne['prix_forfait_mobile'];
            } elseif ($ligne['prix_option_mobile'] <> '') {
                $prix = $ligne['prix_option_mobile'];
            }

            //copier les lignes du pending du mois dernier
            parent::exec("INSERT INTO detail_client (
		code_client_hto, 
		ndi, 
		type_offre, 
		offre, 
		prix, 
		num_sim, rio, code_puk, utilisateur,
		commande_date, 
		engagement, 
		date_souscription, 
		fin_engagement, 
		quota_temps, 
		quota_sms, 
		quota_data, 
		forfait_jours, 
		forfait_horaires, 
		com_temps, 
		com_sms, 
		com_data) 
		SELECT code_client_hto, ndi, '" . $type_offre . "', '" . $offre . "', '" . $prix . "',  num_sim, rio, code_puk, utilisateur, commande_date, engagement, date_souscription, fin_engagement, quota_temps, quota_sms, quota_data, forfait_jours, forfait_horaires, com_temps, com_sms, com_data FROM detail_client_pending WHERE (`option_mobile` <> '' OR `commande_date` < '" . $date_mois_precedent . "') AND id = " . $id . " ");
        }
    }

    /**
     * Selection offre mobile pour modif cote client
     */
    public static function offremobile() {
        return parent::query("select * from ve_offre_mobile union select * from ve_option_mobile");
    }

    public static function optionmobile() {
        return parent::query("select * from ve_option_mobile");
    }

    /*
      public static function getoffreF($id){
      return parent::query("SELECT d.*,v.nom as article,v.code_article FROM detail_client d, ve_offre_mobile v WHERE d.id='".$id."' and d.type_forfait_mobile = v.code_article
      union
      SELECT d.*,v.nom as article,v.code_article FROM detail_client d, ve_option_mobile v WHERE d.id='".$id."' and d.option_mobile = v.code_article
      union
      SELECT d.*,v.nom as article,v.code_article FROM detail_client d, ve_option_mobile v WHERE d.id='".$id."' and d.type_forfait_mobile = v.code_article");
      }
     */

    public static function getoffreF($id) {
        return parent::query("SELECT d.* FROM detail_client d WHERE d.id='" . $id . "' ");
    }

    public static function getoffreO($id) {
        return parent::query("SELECT d.*,v.nom as article,v.code_article FROM detail_client d, ve_option_mobile v WHERE d.id='" . $id . "' and d.offre = v.nom ");
    }

    public static function updateoffre($modif, $ndi, $forfait, $option, $souscription, $id, $utilisateur) {
        if ($modif == "mobile") {
            return parent::exec("UPDATE detail_client set ndi='" . $ndi . "', offre='" . $forfait . "', offre='" . $option . "', date_souscription ='" . $souscription . "', utilisateur ='" . $utilisateur . "'  where id='" . $id . "' ");
        } elseif ($modif == "adsl") {
            return parent::exec("UPDATE detail_client set ndi='" . $ndi . "', offre_xdsl='" . $forfait . "', date_souscription ='" . $souscription . "' where id='" . $id . "' ");
        }
    }
	
	/**
     * Mise à jour d'une offre fixe
     */
	public static function updateOffreFixe($id,$ndi,$forfait,$forfait_prix,$engagement,$date_souscription,$fin_engagement,$utilisateur,$grille_tarifaire,$appels_internes,$reduction_abonnement,$reduction_minute_national,$reduction_minute_international) 
	{
		return parent::exec("UPDATE detail_client SET offre = '".$forfait."', prix = '".$forfait_prix."', engagement = '".$engagement."', date_souscription = '".$date_souscription."', fin_engagement = '".$fin_engagement."', utilisateur = '".$utilisateur."', grille_tarifaire = '".$grille_tarifaire."', appels_internes = '".$appels_internes."', abonnement = '".$reduction_abonnement."', minute_national = '".$reduction_minute_national."', minute_international = '".$reduction_minute_international."' WHERE id='".$id."' ");
    }

    /**
     * Ajout des informations de la ligne fixe dans le détail client
     */
    public static function ajouterLigneFixe($commande_id, $code_client_hto) {
        $lignes_commande         = CommandeDao::getLignesCommandeFixe($commande_id);
        $lignes_commande_options = CommandeDao::getOptionsLignesCommandeFixe($commande_id);
        $lignes_commande         = array_merge($lignes_commande, $lignes_commande_options);

        foreach ($lignes_commande as $key => $ligne_commande) {
            //recuperation des noms des forfaits
            if (in_array($ligne_commande['type_offre'], array('vga', 'voip', 'vga_num'))) {
                $offre     = OffreFixeDao::searchById($ligne_commande['offre']);
                var_dump($offre);
                $offre_nom = $offre[0]['nom'];
            }

            //recuperation des noms des options
            if (in_array($ligne_commande['type_offre'], array('vga_option', 'voip_option', 'vga_num_option'))) {
                $offre     = OffreVenteOptionFixeDao::searchById($ligne_commande['offre']);
                $offre_nom = $offre[0]['nom'];
            }

            if (in_array($ligne_commande['type_offre'], array('vga', 'voip', 'vga_num', 'vga_option', 'voip_option', 'vga_num_option'))) {
                //recupération des variables
                $ndi                  = $ligne_commande['ndi'];
                $sda                  = $ligne_commande['sda'];
                $type_offre           = $ligne_commande['type_offre'];
                $nbr_offre            = $ligne_commande['nbr_offre'];
                $prix                 = $ligne_commande['prix'];
                $identifiant_adv      = $ligne_commande['identifiant_adv'];
                $abonnement           = $ligne_commande ['abonnement'];
                $minute_national      = $ligne_commande['minute_national'];
                $minute_international = $ligne_commande['minute_international'];
                $grille_tarifaire     = $ligne_commande['grille_tarifaire'];
                $fixe                 = $ligne_commande['fixe'];
                $mobile               = $ligne_commande['mobiles'];
                $international        = $ligne_commande['international'];
                $fax_to_mail          = $ligne_commande['fax_to_mail'];
                $forfait_partage      = $ligne_commande['forfait_partage'];

                //gère l'engagement
                $engagement        = $ligne_commande['engagement'];
                $date_souscription = date("d/m/Y");
                $commande_date     = date("Y-m-d H:i:s");
                $fin_engagement    = date("d/m/Y", strtotime("+ " . $engagement . " month", time()));

                //execution de la requête
                parent::exec("INSERT INTO detail_client (
				code_client_hto,
				ndi,
				sda,
				
				type_offre,
				offre,
				nbr_offre,
				prix,
				identifiant_adv,
				abonnement,
				minute_national,
				minute_international,
				grille_tarifaire,		
				commande_date,
				equipement_nom,
				
				engagement,
				date_souscription,
				fin_engagement,
				
				quota_temps,
				forfait_jours,
				forfait_horaires,
				
				fixe,
				mobile,
				international,
				fax_to_mail,
				forfait_partage)
				SELECT
				'" . $code_client_hto . "','" . $ndi . "','" . $sda . "',
				'" . $type_offre . "','" . $offre_nom . "','" . $nbr_offre . "','" . $prix . "','" . $identifiant_adv . "','" . $abonnement . "','" . $minute_national . "','" . $minute_international . "','" . $grille_tarifaire . "',
				'" . $commande_date . "','" . $ligne_commande['id_commande'] . "',
				'" . $engagement . "','" . $date_souscription . "','" . $fin_engagement . "',
				'" . $ligne_commande['voice_status'] . "','" . $ligne_commande['forfait_jours'] . "','" . $ligne_commande['forfait_horaires'] . "',
				'" . $fixe . "','" . $mobile . "','" . $international . "','" . $fax_to_mail . "','" . $forfait_partage . "'
				");
            }
        }return "ok";
    }

    /**
     * Ajout des informations de la ligne ADSL dans le détail client
     */
    public static function ajouterLigneAdsl($commande_id, $code_client_hto) {
        $lignes_commande = CommandeDao::getLignesCommandeAdsl($commande_id);

        foreach ($lignes_commande as $ligne_commande) {
            if (in_array($ligne_commande['type_offre'], array('xdsl_option', 'xdsl'))) {
                $offre_xdsl       = OffreInternetDao::searchById($ligne_commande['offre']);
                $offre            = $offre_xdsl[0]['nom'];
                echo $offre;
                $ndi              = $ligne_commande['ndi'];
                $nb               = $ligne_commande['nbr_offre'];
                $prix             = $ligne_commande['prix'];
                $type_offre       = $ligne_commande['type_offre'];
                $login_xdsl       = $ligne_commande['login_xdsl'];
                $password_xdsl    = $ligne_commande['password_xdsl'];
                $adresse_ip_xdsl  = $ligne_commande['adresse_ip_xdsl'];
                $port             = $ligne_commande['port'];
                $identifiant_adv  = $ligne_commande['identifiant_adv'];
                $abonnement       = $ligne_commande ['abonnement'];
                $grille_tarifaire = $ligne_commande['grille_tarifaire'];

                //gère l'engagement
                $engagement        = $ligne_commande['engagement'];
                $date_souscription = date("d/m/Y");
                $commande_date     = date("Y-m-d H:i:s");
                $fin_engagement    = date("d/m/Y", strtotime("+ " . $engagement . " month", time()));

                parent::exec("INSERT INTO detail_client (
				code_client_hto,
				ndi,
				
				commande_date,
				equipement_nom,
				
				type_offre,
				offre,
				nbr_offre,
				prix,
				login_xdsl,
				password_xdsl,
				adresse_ip_xdsl,
				port,
				identifiant_adv,
				abonnement,
				grille_tarifaire,	
						
				engagement,
				date_souscription,
				fin_engagement
				
				)
				SELECT
				'" . $code_client_hto . "','" . $ndi . "',
				'" . $commande_date . "','" . $ligne_commande['id_commande'] . "',
				'" . $type_offre . "','" . $offre . "','" . $nb . "','" . $prix . "','" . $login_xdsl . "','" . $password_xdsl . "','" . $adresse_ip_xdsl . "','" . $port . "','" . $identifiant_adv . "','" . $abonnement . "','" . $grille_tarifaire . "',
				'" . $engagement . "','" . $date_souscription . "','" . $fin_engagement . "'
				");
            }
        }return "ok";
    }

    /**
     * Ajout des informations de la ligne mobile dans le détail client
     */
    public static function ajouterLigneMobile($code_client_hto, $ndi, $type_forfait_mobile, $prix_forfait_mobile, $commande_date, $equipement_nom, $equipement_prix, $num_sim, $rio, $code_puk, $engagement, $date_souscription, $fin_engagement, $utilisateur, $options_mobile, $quota_temps, $quota_sms, $quota_data, $forfait_jours, $forfait_horaires, $com_temps, $com_sms, $com_data, $com_bb, $com_ir, $reduction_abonnement, $reduction_min_national, $reduction_min_international, $reduction_mobile, $reduction_option_mobile, $reduction_grille_tarifaire) {



        // Forfait principal
        parent::exec('INSERT INTO detail_client(
				code_client_hto,
				ndi,
				type_offre,
				abonnement,
				minute_national,
				minute_international,
				mobil,
				grille_tarifaire,
				offre,
				prix,
				commande_date,
				equipement_nom,
				equipement_prix,
				num_sim,
				rio,
				code_puk,
				engagement,
				date_souscription,
				fin_engagement,
				utilisateur,
				quota_temps,
				quota_sms,
				quota_data,
				forfait_jours,
				forfait_horaires,
				com_temps,
				com_sms,
				com_data,
				com_bb,
				com_ir
		) 
		VALUES(
				"' . $code_client_hto . '",
				"' . $ndi . '",
				"mobile",
				"' . $reduction_abonnement . '",
				"' . $reduction_min_national . '",
				"' . $reduction_min_international . '",
				"' . $reduction_mobile . '",
				"' . $reduction_grille_tarifaire . '",
				"' . $type_forfait_mobile . '",
				"' . $prix_forfait_mobile . '",
				"' . $commande_date . '",
				"' . $equipement_nom . '",
				"' . $equipement_prix . '",
				"' . $num_sim . '",
				"' . $rio . '",
				"' . $code_puk . '",
				"' . $engagement . '",
				"' . $date_souscription . '",
				"' . $fin_engagement . '",
				"' . $utilisateur . '",
				"' . $quota_temps . '",
				"' . $quota_sms . '",
				"' . $quota_data . '",
				"' . $forfait_jours . '",
				"' . $forfait_horaires . '",
				"' . $com_temps . '",
				"' . $com_sms . '",
				"' . $com_data . '",
				"' . $com_bb . '",
				"' . $com_ir . '"
				)');

        // Options
        foreach ($options_mobile as $option_mobile) {
            parent::exec('INSERT INTO detail_client(
				code_client_hto,
				ndi,
				type_offre,
				offre,
				prix,
				engagement,
				date_souscription,
				fin_engagement,
				quota_temps,
				quota_sms,
				quota_data,
				appels_internes
			) VALUES(
				"' . $code_client_hto . '",
				"' . $ndi . '",
				"mobile_option",
				"' . $option_mobile['nom'] . '",
				"' . $option_mobile['prix_seul'] . '",
				"' . $engagement . '",
				"' . $date_souscription . '",
				"' . $fin_engagement . '",
				"' . $option_mobile['sms'] . '",
				"' . $option_mobile['data'] . '",
				"' . $option_mobile['bb'] . '",
				"ok"
			)');
        }
    }

    /*     * ************************************************
     *  Mise à jour / Modification d'une ligne mobile *
     * ************************************************ */

    public static function updateLigneMobileClient($id, $equipement, $engagement, $abonnement_mobile, $options_mobile, $ndi, $client, $sim, $rio, $puk, $utilisateur) {
        $quota['temps']      = null;
        $quota['sms']        = null;
        $quota['data']       = null;
        $forfait['jours']    = null;
        $forfait['horaires'] = null;

        if ($engagement <> '') {
            $date_souscription = date("d/m/Y");
            $fin_engagement    = date("d/m/Y", strtotime("+ " . $engagement . " month", time()));
        } else {
            $ligne             = parent::queryOne('SELECT * FROM detail_client WHERE id = "' . $id . '" ');
            $date_souscription = $ligne['date_souscription'];
            list($day, $month, $year) = explode('/', $date_souscription);
            $timestamp         = mktime(0, 0, 0, $month, $day, $year);

            $engagement     = $ligne['engagement'];
            $fin_engagement = date("d/m/Y", strtotime("+ " . $engagement . " month", $timestamp));
        }

        // Récupération de l'offre -> infos pour la BDD
        $offreMobile = parent::queryOne('SELECT * FROM ve_offre_mobile WHERE id = "' . $abonnement_mobile . '" ');
        if ($offreMobile != null) {
            if ($offreMobile['jours'] <> '') {
                $forfait['jours']                    = $offreMobile['jours'];
                $statut_csv['offre_com_offre_jours'] = $offreMobile['jours'];
            }

            if ($offreMobile['horaires'] <> '') {
                $forfait['horaires']                    = $offreMobile['horaires'];
                $statut_csv['offre_com_offre_horaires'] = $offreMobile['horaires'];
            }

            if (isset($offreMobile['temps'])) {
                $quota['temps']                      = $offreMobile['temps'];
                $offreMobileHA                       = parent::queryOne('SELECT temps, code_article FROM ha_offre_mobile WHERE id = "' . $offreMobile['id_offre_ha'] . '" ');
                $statut_csv['Voice Bundle']          = $offreMobileHA['temps']; //pour le csv
                $statut_csv['code_article']          = $offreMobileHA['code_article'];
                $statut_csv['offre_com_offre_temps'] = $offreMobile['temps'];
            }

            if (isset($offreMobile['sms'])) {
                $quota['sms']                      = $offreMobile['sms'];
                $offreMobileHA                     = parent::queryOne('SELECT sms FROM ha_offre_mobile WHERE id = "' . $offreMobile['id_offre_ha'] . '" ');
                $statut_csv['SMS']                 = $offreMobileHA['sms']; //pour le csv
                $statut_csv['offre_com_offre_SMS'] = $offreMobile['sms'];
            }

            if (isset($offreMobile['data'])) {
                $quota['data']                      = $offreMobile['data'];
                $offreMobileHA                      = parent::queryOne('SELECT data FROM ha_offre_mobile WHERE id = "' . $offreMobile['id_offre_ha'] . '" ');
                $statut_csv['DATA']                 = $offreMobileHA['data']; //pour le csv
                $statut_csv['offre_com_offre_DATA'] = $offreMobile['data'];
            }

            //if(isset($offreMobileHA))
            //{	
            $offreMobileHA       = parent::queryOne('SELECT other FROM ha_offre_mobile WHERE id = "' . $offreMobile['id_offre_ha'] . '" ');
            $statut_csv['other'] = $offreMobileHA['other']; //pour le csv
            //}

            $type_forfait_mobile               = $offreMobile['nom'];
            $statut_csv['offre_com_offre_nom'] = $offreMobile['nom'];
        }

        //tableaux des offres suivant équipement	
        $colonne_offre_ss_terminal = array(
            "0"  => "pv0m_prix_seul",
            "12" => "pv12m_prix_seul",
            "24" => "pv24m_prix_seul",
            "36" => "pv36m_prix_seul"
        );
        $colonne_offre_terminal    = array(
            "0"  => "pv0m_avec_equipement",
            "12" => "pv12m_avec_equipement",
            "24" => "pv24m_avec_equipement",
            "36" => "pv36m_avec_equipement"
        );

        //calcul du prix final de l'offre et du terminal
        if ($equipement <> '') {
            $forfait_prix                       = $offreMobile[$colonne_offre_terminal[$engagement]];
            $statut_csv['offre_com_offre_prix'] = $forfait_prix;
        } else {
            $forfait_prix                       = $offreMobile[$colonne_offre_ss_terminal[$engagement]];
            $statut_csv['offre_com_offre_prix'] = $forfait_prix;
        }

        //mise à jour de la ligne
        /*
          parent::exec('UPDATE detail_client_pending SET
          type_forfait_mobile="'.$type_forfait_mobile.'",
          prix_forfait_mobile="'.$forfait_prix.'",
          engagement="'.$engagement.'",
          date_souscription="'.$date_souscription.'",
          fin_engagement="'.$fin_engagement.'",
          quota_temps="'.$quota['temps'].'",
          quota_sms="'.$quota['sms'].'",
          quota_data="'.$quota['data'].'",
          forfait_jours="'.$forfait['jours'].'",
          forfait_horaires="'.$forfait['horaires'].'",
          com_temps="'.$statut_csv['Voice Bundle'].'",
          com_sms="'.$statut_csv['SMS'].'",
          com_data="'.$statut_csv['DATA'].'"
          WHERE id="'.$id.'"');
         */

        //suppression des options en base
        CommandeDao::delete("detail_client_pending", "ndi=" . $ndi . "");

        parent::exec('INSERT INTO detail_client_pending (id,code_client_hto, ndi, type_forfait_mobile, prix_forfait_mobile, commande_date, num_sim, rio, code_puk, engagement, date_souscription, fin_engagement, utilisateur, quota_temps, quota_sms, quota_data, forfait_jours, forfait_horaires, com_temps, com_sms, com_data) VALUES ("' . $id . '","' . $client . '", "' . $ndi . '", "' . $type_forfait_mobile . '","' . $forfait_prix . '", "' . date("Y-m-d H:i:s", time()) . '","' . $sim . '","' . $rio . '","' . $puk . '","' . $engagement . '","' . $date_souscription . '","' . $fin_engagement . '","' . $utilisateur . '","' . $quota['temps'] . '","' . $quota['sms'] . '","' . $quota['data'] . '","' . $forfait['jours'] . '","' . $forfait['horaires'] . '","' . $statut_csv['Voice Bundle'] . '","' . $statut_csv['SMS'] . '","' . $statut_csv['DATA'] . '") ');

        // Récupération des options et insertion en base
        if (isset($options_mobile['options_mobile_temps'])) {
            $optionsMobile['temps']             = parent::queryOne('SELECT * FROM ve_option_mobile WHERE id = "' . $options_mobile['options_mobile_temps'] . '" ');
            $statut_csv['offre_com_option_nom'] = $optionsMobile['temps']['nom'];

            //pour le csv
            $optionsMobileHA            = parent::queryOne('SELECT temps FROM ha_option_mobile WHERE id = "' . $optionsMobile['temps']['id_option_ha'] . '" ');
            $statut_csv['Voice Bundle'] = $optionsMobileHA['temps'];

            //pour la BDD
            if ($equipement <> '') {
                $option_prix                         = $optionsMobile['temps']['prix_avec_equipement'];
                $statut_csv['offre_com_option_prix'] = $option_prix;
            } else {
                $option_prix                         = $optionsMobile['temps']['prix_seul'];
                $statut_csv['offre_com_option_prix'] = $option_prix;
            }
            echo $client;
            //insertion en base	des options	
            parent::exec('INSERT INTO detail_client_pending (
			code_client_hto, 
			ndi, 
			commande_date,
			option_mobile, 
			prix_option_mobile, 
			engagement , 
			date_souscription, 
			fin_engagement, 
			quota_temps, 
			quota_sms,
			quota_data,
			com_temps,
			com_sms,
			com_data,
			com_bb,
			com_ir
			) VALUES ("' . $client . '","' . $ndi . '", "' . date("Y-m-d H:i:s", time()) . '","' . $optionsMobile['temps']['nom'] . '","' . $option_prix . '","' . $engagement . '","' . $date_souscription . '","' . $fin_engagement . '","' . $optionsMobile['temps']['temps'] . '","' . $optionsMobile['temps']['sms'] . '","' . $optionsMobile['temps']['data'] . '","' . $statut_csv['Voice Bundle'] . '","","","","")');
        }

        if (isset($options_mobile['options_mobile_sms'])) {
            $optionsMobile['sms']               = parent::queryOne('SELECT * FROM ve_option_mobile WHERE id = "' . $options_mobile['options_mobile_sms'] . '" ');
            $statut_csv['offre_com_option_nom'] = $optionsMobile['sms']['nom'];


            //pour le csv
            $optionsMobileHA                = parent::queryOne('SELECT sms,code_article,nom,prix_seul FROM ha_option_mobile WHERE id = "' . $optionsMobile['sms']['id_option_ha'] . '" ');
            $statut_csv['SMS']              = $optionsMobileHA['sms'];
            $statut_csv['SMS_code_article'] = $optionsMobileHA['code_article'];
            $statut_csv['SMS_nom']          = $optionsMobileHA['nom'];
            $statut_csv['SMS_prix']         = $optionsMobileHA['prix_seul'];

            //pour la BDD
            if ($equipement <> '') {
                $option_prix                         = $optionsMobile['sms']['prix_avec_equipement'];
                $statut_csv['offre_com_option_prix'] = $option_prix;
            } else {
                $option_prix                         = $optionsMobile['sms']['prix_seul'];
                $statut_csv['offre_com_option_prix'] = $option_prix;
            }

            //insertion en base	des options	
            parent::exec('INSERT INTO detail_client_pending (code_client_hto,ndi,commande_date,option_mobile,prix_option_mobile,engagement,date_souscription,fin_engagement,quota_temps,quota_sms,quota_data,com_temps,com_sms,com_data,com_bb,com_ir) VALUES ("' . $client . '","' . $ndi . '", "' . date("Y-m-d H:i:s", time()) . '","' . $optionsMobile['sms']['nom'] . '","' . $option_prix . '","' . $engagement . '","' . $date_souscription . '","' . $fin_engagement . '","' . $optionsMobile['sms']['temps'] . '","' . $optionsMobile['sms']['sms'] . '","' . $optionsMobile['sms']['data'] . '","","' . $statut_csv['SMS'] . '","","","")');
        }

        if (isset($options_mobile['options_mobile_data'])) {
            $optionsMobile['data']              = parent::queryOne('SELECT * FROM ve_option_mobile WHERE id = "' . $options_mobile['options_mobile_data'] . '" ');
            $statut_csv['offre_com_option_nom'] = $optionsMobile['data']['nom'];

            //pour le csv
            $optionsMobileHA                 = parent::queryOne('SELECT data,code_article,nom,prix_seul FROM ha_option_mobile WHERE id = "' . $optionsMobile['data']['id_option_ha'] . '" ');
            $statut_csv['DATA']              = $optionsMobileHA['data'];
            $statut_csv['DATA_code_article'] = $optionsMobileHA['code_article'];
            $statut_csv['DATA_nom']          = $optionsMobileHA['nom'];
            $statut_csv['DATA_prix']         = $optionsMobileHA['prix_seul'];

            //pour la BDD
            if ($equipement <> '') {
                $option_prix                         = $optionsMobile['data']['prix_avec_equipement'];
                $statut_csv['offre_com_option_prix'] = $option_prix;
            } else {
                $option_prix                         = $optionsMobile['data']['prix_seul'];
                $statut_csv['offre_com_option_prix'] = $option_prix;
            }

            //insertion en base	des options	
            parent::exec('INSERT INTO detail_client_pending (code_client_hto,ndi,commande_date,option_mobile,prix_option_mobile,engagement,date_souscription,fin_engagement,quota_temps,quota_sms,quota_data,com_temps,com_sms,com_data,com_bb,com_ir) VALUES ("' . $client . '","' . $ndi . '", "' . date("Y-m-d H:i:s", time()) . '","' . $optionsMobile['data']['nom'] . '","' . $option_prix . '","' . $engagement . '","' . $date_souscription . '","' . $fin_engagement . '","' . $optionsMobile['data']['temps'] . '","' . $optionsMobile['data']['sms'] . '","' . $optionsMobile['data']['data'] . '","","","' . $statut_csv['DATA'] . '","","")');
        }

        if (isset($options_mobile['options_mobile_bb'])) {
            $optionsMobile['bb']                = parent::queryOne('SELECT * FROM ve_option_mobile WHERE id = "' . $options_mobile['options_mobile_bb'] . '" ');
            $statut_csv['offre_com_option_nom'] = $optionsMobile['bb']['nom'];

            //pour le csv
            $optionsMobileHA                       = parent::queryOne('SELECT bb,code_article,nom,prix_seul FROM ha_option_mobile WHERE id = "' . $optionsMobile['bb']['id_option_ha'] . '" ');
            $statut_csv['Blackberry']              = $optionsMobileHA['bb'];
            $statut_csv['Blackberry_code_article'] = $optionsMobileHA['code_article'];
            $statut_csv['BB_nom']                  = $optionsMobileHA['nom'];
            $statut_csv['BB_prix']                 = $optionsMobileHA['prix_seul'];

            //pour la BDD
            if ($equipement <> '') {
                $option_prix                         = $optionsMobile['bb']['prix_avec_equipement'];
                $statut_csv['offre_com_option_prix'] = $option_prix;
            } else {
                $option_prix                         = $optionsMobile['bb']['prix_seul'];
                $statut_csv['offre_com_option_prix'] = $option_prix;
            }

            //insertion en base	des options	
            parent::exec('INSERT INTO detail_client_pending (code_client_hto,ndi,commande_date,option_mobile,prix_option_mobile,engagement,date_souscription,fin_engagement,quota_temps,quota_sms,quota_data,com_temps,com_sms,com_data,com_bb,com_ir) VALUES ("' . $client . '","' . $ndi . '", "' . date("Y-m-d H:i:s", time()) . '","' . $optionsMobile['bb']['nom'] . '","' . $option_prix . '","' . $engagement . '","' . $date_souscription . '","' . $fin_engagement . '","' . $optionsMobile['bb']['temps'] . '","' . $optionsMobile['bb']['sms'] . '","' . $optionsMobile['bb']['data'] . '","","","","' . $statut_csv['Blackberry'] . '","")');
        }

        if (isset($options_mobile['options_mobile_international'])) {
            $optionsMobile['international']     = parent::queryOne('SELECT * FROM ve_option_mobile WHERE id = "' . $options_mobile['options_mobile_international'] . '" ');
            $statut_csv['offre_com_option_nom'] = $optionsMobile['international']['nom'];

            //pour le csv
            $optionsMobileHA                           = parent::queryOne('SELECT international,code_article,nom,prix_seul FROM ha_option_mobile WHERE id = "' . $optionsMobile['international']['id_option_ha'] . '" ');
            $statut_csv['Foreign Status']              = $optionsMobileHA['international'];
            $statut_csv['Foreign Status_code_article'] = $optionsMobileHA['code_article'];
            $statut_csv['international_nom']           = $optionsMobileHA['nom'];
            $statut_csv['international_prix']          = $optionsMobileHA['prix_seul'];

            //pour la BDD
            if ($equipement <> '') {
                $option_prix                         = $optionsMobile['international']['prix_avec_equipement'];
                $statut_csv['offre_com_option_prix'] = $option_prix;
            } else {
                $option_prix                         = $optionsMobile['international']['prix_seul'];
                $statut_csv['offre_com_option_prix'] = $option_prix;
            }

            //insertion en base	des options	
            parent::exec('INSERT INTO detail_client_pending (code_client_hto,ndi,commande_date,option_mobile,prix_option_mobile,engagement,date_souscription,fin_engagement,quota_temps,quota_sms,quota_data,com_temps,com_sms,com_data,com_bb,com_ir) VALUES ("' . $client . '","' . $ndi . '", "' . date("Y-m-d H:i:s", time()) . '","' . $optionsMobile['international']['nom'] . '","' . $option_prix . '","' . $engagement . '","' . $date_souscription . '","' . $fin_engagement . '","' . $optionsMobile['international']['temps'] . '","' . $optionsMobile['international']['sms'] . '","' . $optionsMobile['international']['data'] . '","","","","","' . $statut_csv['Foreign Status'] . '")');
        }

        if (isset($options_mobile['options_mobile_interne'])) {
            $optionsMobile['interne']           = parent::queryOne('SELECT * FROM ve_option_mobile WHERE id = "' . $options_mobile['options_mobile_interne'] . '" ');
            $statut_csv['offre_com_option_nom'] = $optionsMobile['interne']['nom'];

            //pour le csv
            //$optionsMobileHA = parent::queryOne('SELECT international FROM ha_option_mobile WHERE id = "'.$optionsMobile['international']['id_option_ha'].'" ');
            //$statut_csv['Foreign Status'] = $optionsMobileHA['international']; 
            //pour la BDD
            if ($equipement <> '') {
                $option_prix                         = $optionsMobile['interne']['prix_avec_equipement'];
                $statut_csv['offre_com_option_prix'] = $option_prix;
            } else {
                $option_prix                         = $optionsMobile['interne']['prix_seul'];
                $statut_csv['offre_com_option_prix'] = $option_prix;
            }

            //insertion en base	des options	
            parent::exec('INSERT INTO detail_client_pending (code_client_hto,ndi,commande_date,option_mobile,prix_option_mobile,engagement,date_souscription,fin_engagement,quota_temps,quota_sms,quota_data,com_temps,com_sms,com_data,com_bb,com_ir) VALUES ("' . $client . '","' . $ndi . '", "' . date("Y-m-d H:i:s", time()) . '","' . $optionsMobile['interne']['nom'] . '","' . $option_prix . '","' . $engagement . '","' . $date_souscription . '","' . $fin_engagement . '","","","","","","","","")');
        }

        //si changement de csv, renvoi des éléments
        if (isset($statut_csv)) {
            return $statut_csv;
        } else {
            return true;
        }
    }

}
